package com.google.code.sms.dao.schema;

import com.google.code.sms.Logger;
import org.springframework.jdbc.core.*;

/**
 * Creates the base database schema
 * 
 * @author scott2ware
 */
public class BaseSchema extends Schema {
    
    private static final Logger LOG = Logger.getLogger("BaseSchema");
    
    @Override
    public void execute(JdbcTemplate template) {

        // Role
        if (!tableExists(template, "role")) {
            LOG.info("Database table 'role' not found.  Creating it.");
            template.execute("create table role ("
                    + "id int not null,"
                    + "name varchar not null,"
                    + "primary key (id))");
            template.execute("insert into role values (1, 'admin')");
            template.execute("insert into role values (2, 'download')");
            template.execute("insert into role values (3, 'playlist')");
            template.execute("insert into role values (4, 'stream')");
            template.execute("insert into role values (5, 'settings')");
            LOG.info("Database table 'role' was created successfully.");
        }

        // User
        if (!tableExists(template, "user")) {
            LOG.info("Database table 'user' not found.  Creating it.");
            template.execute("create table user ("
                    + "username varchar not null,"
                    + "password varchar not null,"
                    + "bytes_streamed bigint default 0 not null,"
                    + "bytes_downloaded bigint default 0 not null,"
                    + "primary key (username))");
            template.execute("insert into user values ('admin', 'admin', 0, 0)");
            LOG.info("Database table 'user' was created successfully.");
        }
        
        // User Role
        if (!tableExists(template, "user_role")) {
            LOG.info("Database table 'user_role' not found.  Creating it.");
            template.execute("create table user_role ("
                    + "username varchar not null,"
                    + "role_id int not null,"
                    + "primary key (username, role_id),"
                    + "foreign key (username) references user(username),"
                    + "foreign key (role_id) references role(id))");
            template.execute("insert into user_role values ('admin', 1)");
            template.execute("insert into user_role values ('admin', 2)");
            template.execute("insert into user_role values ('admin', 3)");
            template.execute("insert into user_role values ('admin', 4)");
            template.execute("insert into user_role values ('admin', 5)");
            LOG.info("Database table 'user_role' was created successfully.");
        }

        // Player
        if (!tableExists(template, "player")) {
            LOG.info("Database table 'player' not found.  Creating it.");
            template.execute("create table player ("
                    + "id int not null,"
                    + "name varchar,"
                    + "type varchar,"
                    + "client_id varchar,"
                    + "technology varchar default 'WEB' not null,"
                    + "username varchar,"
                    + "ip_address varchar,"
                    + "dynamic_ip boolean default true not null,"
                    + "auto_control_enabled boolean not null,"
                    + "last_seen datetime,"
                    + "cover_art_scheme varchar not null,"
                    + "client_side_playlist boolean default false not null,"
                    + "primary key (id))");
            LOG.info("Database table 'player' was created successfully.");
        }
        
        // Media Folder
        if (!tableExists(template, "media_folder")) {
            LOG.info("Database table 'media_folder' not found.  Creating it.");
            template.execute("create table media_folder ("
                    + "id identity,"
                    + "path varchar not null,"
                    + "name varchar not null,"
                    + "changed datetime default 0 not null,"
                    + "enabled boolean not null)");
            LOG.info("Database table 'media_folder' was created successfully.");
        }
        
        // Media Element
        if (!tableExists(template, "media_element")) {
            LOG.info("Database table 'media_element' not found.  Creating it.");
            template.execute("create cached table media_element ("
                    + "id identity,"
                    + "element_type varchar not null,"
                    + "name varchar not null,"
                    + "path varchar not null,"
                    + "folder varchar,"
                    + "parent_path varchar,"
                    + "parent_name varchar,"
                    + "play_count int not null,"
                    + "last_played datetime,"
                    + "created datetime not null,"
                    + "changed datetime not null,"
                    + "last_scanned datetime not null,"
                    + "children_last_updated datetime not null,"
                    + "present boolean not null,"
                    + "format varchar,"
                    + "file_size bigint,"
                    + "unique (path))");

            template.execute("create index idx_media_element_name on media_element(name)");
            template.execute("create index idx_media_element_path on media_element(path)");
            template.execute("create index idx_media_element_parent_path on media_element(parent_path)");
            template.execute("create index idx_media_element_present on media_element(present)");
            template.execute("create index idx_media_element_play_count on media_element(play_count)");
            template.execute("create index idx_media_element_created on media_element(created)");
            template.execute("create index idx_media_element_last_played on media_element(last_played)");

            LOG.info("Database table 'media_element' was created successfully.");
        }
        
        // Directory Meta-Data
        if(!tableExists(template, "directory_metadata")) {
            LOG.info("Database table 'directory_metadata' not found.  Creating it.");
            template.execute("create cached table directory_metadata ("
                    + "media_element_id int not null,"
                    + "media_type varchar not null,"
                    + "cover_art_path varchar,"
                    + "primary key (media_element_id),"
                    + "foreign key (media_element_id) references media_element(id) on delete cascade)");
            
            template.execute("create index idx_directory_metadata_media_type on directory_metadata(media_type)");
            
            LOG.info("Database table 'directory_metadata' was created successfully.");

        }
        
        // Audio Meta-Data
        if (!tableExists(template, "audio_metadata")) {
            LOG.info("Database table 'audio_metadata' not found.  Creating it.");
            template.execute("create cached table audio_metadata ("
                    + "media_element_id int not null,"
                    + "title varchar,"
                    + "artist varchar,"
                    + "album_artist varchar,"
                    + "album varchar,"
                    + "comment varchar,"
                    + "genre varchar,"
                    + "year int,"
                    + "disc_number int,"
                    + "disc_subtitle varchar,"
                    + "track_number int,"
                    + "bit_rate int,"
                    + "duration int,"
                    + "cover_art_path varchar,"
                    + "codec varchar,"
                    + "sample_rate int,"
                    + "configuration varchar,"
                    + "primary key (media_element_id),"
                    + "foreign key (media_element_id) references media_element(id) on delete cascade)");
            
            template.execute("create index idx_audio_metadata_album on audio_metadata(album)");
            template.execute("create index idx_audio_metadata_artist on audio_metadata(artist)");
            template.execute("create index idx_audio_metadata_album_artist on audio_metadata(album_artist)");
            template.execute("create index idx_audio_metadata_genre on audio_metadata(genre)");

            LOG.info("Database table 'audio_metadata' was created successfully.");
        }
        
        // Video Meta-Data
        if (!tableExists(template, "video_metadata")) {
            LOG.info("Database table 'video_metadata' not found.  Creating it.");
            template.execute("create cached table video_metadata ("
                    + "media_element_id identity,"
                    + "title varchar,"
                    + "bit_rate int,"
                    + "duration int,"
                    + "width int,"
                    + "height int,"
                    + "year int,"
                    + "cover_art_path varchar,"
                    + "poster_path varchar,"
                    + "subtitles_path varchar,"
                    + "video_codec varchar,"
                    + "audio_codec varchar,"
                    + "audio_sample_rate int,"
                    + "audio_configuration varchar,"
                    + "rating float,"
                    + "description varchar,"
                    + "tagline varchar,"
                    + "certificate varchar,"
                    + "primary key (media_element_id),"
                    + "foreign key (media_element_id) references media_element(id) on delete cascade)");
            
            LOG.info("Database table 'video_metadata' was created successfully.");
        }
        
        // Playlist
        if (!tableExists(template, "playlist")) {
            LOG.info("Database table 'playlist' not found.  Creating it.");
            template.execute("create table playlist ("
                    + "id identity,"
                    + "username varchar not null,"
                    + "is_public boolean not null,"
                    + "name varchar not null,"
                    + "element_count int default 0 not null,"
                    + "duration int default 0 not null,"
                    + "created datetime not null,"
                    + "changed datetime not null,"
                    + "foreign key (username) references user(username) on delete cascade)");

            LOG.info("Database table 'playlist' was created successfully.");
        }

        // Playlist Element
        if (!tableExists(template, "playlist_element")) {
            LOG.info("Database table 'playlist_element' not found.  Creating it.");
            template.execute("create cached table playlist_element ("
                    + "id identity,"
                    + "playlist_id int not null,"
                    + "media_element_id int not null,"
                    + "foreign key (playlist_id) references playlist(id) on delete cascade,"
                    + "foreign key (media_element_id) references media_element(id) on delete cascade)");

            LOG.info("Database table 'playlist_element' was created successfully.");
        }

        // Playlist User
        if (!tableExists(template, "playlist_user")) {
            LOG.info("Database table 'playlist_user' not found.  Creating it.");
            template.execute("create table playlist_user ("
                    + "id identity,"
                    + "playlist_id int not null,"
                    + "username varchar not null,"
                    + "unique(playlist_id, username),"
                    + "foreign key (playlist_id) references playlist(id) on delete cascade,"
                    + "foreign key (username) references user(username) on delete cascade)");

            LOG.info("Database table 'playlist_user' was created successfully.");
        }
    }
}
